var conn = require('../javascripts/connect');
var calculation1 = {};
calculation1.cal = async function (depYM) {
    let needCal = [];
    let [depYM0, year, month] = depYM.split("_");
    month = Number(month);
    year = Number(year);
    depYM1 = `管理费用_${year}_${month}`;
    depYM2 = `总部经营管理部_${year}_${month}`;
    let depFiled = "去年同期抓取";
    needCal[0] = `UPDATE ${depYM} ZB,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号) YTH SET ZB.${depFiled}=YTH.A,ZB.年份=${year},ZB.月份=${month},ZB.修改日期=now()  WHERE ZB.行号=YTH.行号 AND ZB.行号!=131`;
    needCal[1] = `UPDATE ${depYM} AS ZB SET ZB.${depFiled}=(SELECT SUM(B) FROM (SELECT B.行号,B.${depFiled} B FROM ${depYM2} B WHERE B.行号=61 OR B.行号=128) YTH) WHERE ZB.行号=131`;
    depFiled = "去年同期调整";
    needCal[2] = `UPDATE ${depYM} ZB,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号) YTH SET ZB.${depFiled}=YTH.A,ZB.年份=${year},ZB.月份=${month},ZB.修改日期=now()  WHERE ZB.行号=YTH.行号 AND ZB.行号!=131`;
    needCal[3] = `UPDATE ${depYM} AS ZB SET ZB.${depFiled}=(SELECT SUM(B) FROM (SELECT B.行号,B.${depFiled} B FROM ${depYM2} B WHERE B.行号=61 OR B.行号=128) YTH) WHERE ZB.行号=131`;
    console.log("needCal[3]", needCal[3])
    depFiled = "去年同期累计";
    needCal[4] = `UPDATE ${depYM} ZB,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号) YTH SET ZB.${depFiled}=YTH.A,ZB.年份=${year},ZB.月份=${month},ZB.修改日期=now()  WHERE ZB.行号=YTH.行号 AND ZB.行号!=131`;
    needCal[5] = `UPDATE ${depYM} AS ZB SET ZB.${depFiled}=(SELECT SUM(B) FROM (SELECT B.行号,B.${depFiled} B FROM ${depYM2} B WHERE B.行号=61 OR B.行号=128) YTH) WHERE ZB.行号=131`;

    depFiled = "年度计划金额";
    needCal[6] = `UPDATE ${depYM} ZB,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号) YTH SET ZB.${depFiled}=YTH.A,ZB.年份=${year},ZB.月份=${month},ZB.修改日期=now()  WHERE ZB.行号=YTH.行号`;
    needCal[7] = `UPDATE ${depYM} AS ZB SET ZB.${depFiled}=(SELECT SUM(B) FROM (SELECT B.行号,B.${depFiled} B FROM ${depYM2} B WHERE B.行号=61 OR B.行号=128) YTH) WHERE ZB.行号=131`;

    depFiled = "当月累计金额";
    needCal[8] = `UPDATE ${depYM} ZB,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号) YTH SET ZB.${depFiled}=YTH.A,ZB.年份=${year},ZB.月份=${month},ZB.修改日期=now()  WHERE ZB.行号=YTH.行号 AND ZB.行号!=131`;
    needCal[9] = `UPDATE ${depYM} AS ZB SET ZB.${depFiled}=(SELECT SUM(B) FROM (SELECT B.行号,B.${depFiled} B FROM ${depYM2} B WHERE B.行号=61 OR B.行号=128) YTH) WHERE ZB.行号=131`;

    depFiled = "上月抓取值";
    needCal[10] = `UPDATE ${depYM} ZB,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号) YTH SET ZB.${depFiled}=YTH.A,ZB.年份=${year},ZB.月份=${month},ZB.修改日期=now()  WHERE ZB.行号=YTH.行号 AND ZB.行号!=131`;
    needCal[11] = `UPDATE ${depYM} AS ZB SET ZB.${depFiled}=(SELECT SUM(B) FROM (SELECT B.行号,B.${depFiled} B FROM ${depYM2} B WHERE B.行号=61 OR B.行号=128) YTH) WHERE ZB.行号=131`;

    depFiled = "上月调整值";
    needCal[12] = `UPDATE ${depYM} ZB,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号) YTH SET ZB.${depFiled}=YTH.A,ZB.年份=${year},ZB.月份=${month},ZB.修改日期=now()  WHERE ZB.行号=YTH.行号 AND ZB.行号!=131`;
    needCal[13] = `UPDATE ${depYM} AS ZB SET ZB.${depFiled}=(SELECT SUM(B) FROM (SELECT B.行号,B.${depFiled} B FROM ${depYM2} B WHERE B.行号=61 OR B.行号=128) YTH) WHERE ZB.行号=131`;

    depFiled = "当月计划值";
    needCal[14] = `UPDATE ${depYM} ZB,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号) YTH SET ZB.${depFiled}=YTH.A,ZB.年份=${year},ZB.月份=${month},ZB.修改日期=now()  WHERE ZB.行号=YTH.行号`;
    needCal[15] = `UPDATE ${depYM} AS ZB SET ZB.${depFiled}=(SELECT SUM(B) FROM (SELECT B.行号,B.${depFiled} B FROM ${depYM2} B WHERE B.行号=61 OR B.行号=128) YTH) WHERE ZB.行号=131`;

    depFiled = "当月抓取值";
    needCal[16] = `UPDATE ${depYM} ZB,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号) YTH SET ZB.${depFiled}=YTH.A,ZB.年份=${year},ZB.月份=${month},ZB.修改日期=now()  WHERE ZB.行号=YTH.行号 AND ZB.行号!=131`;
    needCal[17] = `UPDATE ${depYM} AS ZB SET ZB.${depFiled}=(SELECT SUM(B) FROM (SELECT B.行号,B.${depFiled} B FROM ${depYM2} B WHERE B.行号=61 OR B.行号=128) YTH) WHERE ZB.行号=131`;

    depFiled = "当月调整值";
    needCal[18] = `UPDATE ${depYM} ZB,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号) YTH SET ZB.${depFiled}=YTH.A,ZB.年份=${year},ZB.月份=${month},ZB.修改日期=now()  WHERE ZB.行号=YTH.行号 AND ZB.行号!=131`;
    needCal[19] = `UPDATE ${depYM} AS ZB SET ZB.${depFiled}=(SELECT SUM(B) FROM (SELECT B.行号,B.${depFiled} B FROM ${depYM2} B WHERE B.行号=61 OR B.行号=128) YTH) WHERE ZB.行号=131`;

    depFiled = "下月计划值";
    needCal[20] = `UPDATE ${depYM} ZB,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号) YTH SET ZB.${depFiled}=YTH.A,ZB.年份=${year},ZB.月份=${month},ZB.修改日期=now()  WHERE ZB.行号=YTH.行号`;
    needCal[21] = `UPDATE ${depYM} AS ZB SET ZB.${depFiled}=(SELECT SUM(B) FROM (SELECT B.行号,B.${depFiled} B FROM ${depYM2} B WHERE B.行号=61 OR B.行号=128) YTH) WHERE ZB.行号=131`;
    for (let i = 0; i < needCal.length; i++) {
        await (async function (i) {
            await new Promise((res1, rej1) => {
                console.log("needCal[i]", i, needCal[i]);
                conn.connection2.query(needCal[i], function (err, result, files) {
                    if (err) {
                        console.log('[ERROR UPDATE]', err.message);
                        return;
                    }
                    console.log("汇总成功", result);
                    res1(true);
                })
            })
        })(i)
    }
    return true;
}
module.exports = {
    calculation1
};